1 link tagged with all of: postgresql + query optimization + union + database + materialized
Click any tag below to further narrow down your results
Links
A developer measured five common SQL patterns that force PostgreSQL to do unnecessary work—UNION instead of UNION ALL, MATERIALIZED CTEs, ORDER BY random(), SELECT * instead of specific columns, and unindexed expressions—and found gaps ranging from 20x to 4,000x slower on a 2 million row table.
- UNION deduplicated 2 million rows unnecessarily (13.6s) versus UNION ALL (0.68s) when the developer knew the halves couldn't overlap
- MATERIALIZED keyword forced PostgreSQL to build the entire CTE before filtering, turning a 0.24ms query into 1,973ms
- ORDER BY random() LIMIT 1 sorted all 2 million rows to get one (1,313ms); TABLESAMPLE SYSTEM did it in 0.3ms, though with different guarantees
- SELECT * required visiting the table for every row (1,718 buffers, 64.8ms) versus querying just the indexed column (279 buffers, 32.8ms)